/* eslint-disable class-methods-use-this */
/* eslint-disable prefer-template */

import { SqlHelper } from '../../lib/utils/sqlHelper'
import { DbColumn } from '../../models/dbColumn'
import { DbForeignKey } from '../../models/dbForeignKey'
import { DbIndex } from '../../models/dbIndex'
import { DbModel } from '../../models/dbModel'
import { DbTable } from '../../models/dbTable'
import { DbProvider, line } from '../core/dbProvider'

export class OracleProvider extends DbProvider {
	private SP_REMVOE_FK_NAME = 'tmp_dbm_drop_table_fk'

	private MAX_OBJECT_NAME_LENGTH = 30

	private SQL_DELIMITER = '/'

	private p_colObjectNames: any = {}

	public getCreateTableIfNotExistsSql(model: DbModel): string {
		this.p_colObjectNames = {}
		this.outputInitialize()

		this.outputlnln(this.getSqlCreateTableIfNotExistsSinglePre())
		model.tables.forEach((table: DbTable) => {
			const { sqlCreateTable, sqlCreateFK } = this.getSqlCreateTableIfNotExistsSingle(table)
			this.outputlnln(sqlCreateTable)
			if (sqlCreateFK.length > 0) {
				this.outputlnln(sqlCreateFK, 1)
			}
		})

		this.outputln(this.getSqlCreateTableIfNotExistsSinglePost())
		return this.outputCopy()
	}

	public getCreateTableSql(model: DbModel, withComment: boolean): string {
		this.outputInitialize()
		this.getCreateTableSqlInternal(model, withComment)
		return this.outputCopy()
	}

	private getCreateTableSqlInternal(model: DbModel, withComment: boolean) {
		this.p_colObjectNames = {}
		// Create tables
		model.tables.forEach((table: DbTable) => {
			const { sqlCreateTable, sqlCreateFK } = this.getSqlCreateTableSingle(table, withComment)
			this.outputlnln(sqlCreateTable)
			if (sqlCreateFK) {
				this.outputlnln(sqlCreateFK, 1)
			}
		})
	}

	public getDropTableSql(model: DbModel): string {
		let sSQL: string
		this.outputInitialize()

		// Create the store procedure of dropping foreign key
		sSQL = this.getSqlRemoveFKPre()
		this.outputlnln(sSQL + line + this.SQL_DELIMITER)

		// Drop foreign key relation
		model.tables.forEach((table: DbTable) => {
			sSQL = this.getSqlRemoveFkSingle(table)
			this.outputlnln(sSQL)
		})

		// Drop the store procedure of dropping foreign key
		sSQL = this.getSqlRemoveFKPost()
		this.outputlnln(sSQL)

		// Drop tables
		this.outputlnln(this.getSqlDropTableSinglePre())
		model.tables.forEach((table: DbTable) => {
			sSQL = this.getSqlDropTableSingle(table)
			this.outputlnln(sSQL)
		})
		this.outputlnln(this.getSqlDropTableSinglePost())

		return this.outputCopy()
	}

	public getDropThenCreateTableSql(model: DbModel, withComment: boolean): string {
		let sSQL: string

		this.outputInitialize()

		// Create the store procedure of dropping foreign key
		sSQL = this.getSqlRemoveFKPre()
		this.outputlnln(sSQL + line + this.SQL_DELIMITER)

		// Drop foreign key relation
		model.tables.forEach((table: DbTable) => {
			sSQL = this.getSqlRemoveFkSingle(table)
			this.outputlnln(sSQL)
		})

		// Drop the store procedure of dropping foreign key
		sSQL = this.getSqlRemoveFKPost()
		this.outputlnln(sSQL)

		// Drop tables
		this.outputlnln(this.getSqlDropTableSinglePre())
		model.tables.forEach((table: DbTable) => {
			sSQL = this.getSqlDropTableSingle(table)
			this.outputlnln(sSQL)
		})
		this.outputlnln(this.getSqlDropTableSinglePost())

		// Create tables
		this.getCreateTableSqlInternal(model, withComment)

		return this.outputCopy()
	}

	private getSqlCreateTableIfNotExistsSingle(table: DbTable): {
		sqlCreateFK: string
		sqlCreateTable: string
	} {
		const result = this.getSqlCreateTableSingle(table, false)
		const sSQL =
			'  SELECT COUNT(*) INTO v_table_is_exists' +
			line +
			'  FROM user_tables' +
			line +
			`  WHERE lower(table_name) = lower('${table.name}');` +
			line +
			'  IF v_table_is_exists != 0 THEN' +
			line +
			`    execute immediate ${result.sqlCreateTable}` +
			line +
			'  END IF;'

		result.sqlCreateTable = sSQL
		return result
	}

	private getSqlCreateTableIfNotExistsSinglePost(): string {
		const sSQL = 'END;' + line + this.SQL_DELIMITER
		return sSQL
	}

	private getSqlCreateTableIfNotExistsSinglePre(): string {
		const sSQL = 'DECLARE' + line + '  v_table_is_exists integer;' + line + 'BEGIN'

		return sSQL
	}

	public getSqlCreateTableSingle(
		table: DbTable,
		withComment: boolean,
	): {
		sqlCreateFK: string
		sqlCreateTable: string
	} {
		const result = {
			sqlCreateFK: '',
			sqlCreateTable: '',
		}

		if (!table.columns) {
			return result
		}
		// Create Columns sql
		let sqlColumn = ''
		table.columns.forEach((column: DbColumn, index) => {
			const sqlComma = index === 0 ? ' ' : ','
			const sqlNullable = column.nullable ? 'NULL' : 'NOT NULL'
			const sqlDefault = column.default!.length === 0 ? '' : ` DEFAULT ${column.default}`
			sqlColumn += line + `  ${sqlComma}${column.name} ${column.type} ${sqlDefault}${sqlNullable}`
		})

		// Primary key SQL
		let sqlPrimaryKey = ''
		if (table.primaryKey) {
			const sqlPkName = this.renderPkName(table)
			const sqlPkColumns = table.primaryKey.columns.replace(',\\s*', ', ')
			sqlPrimaryKey =
				line +
				`ALTER TABLE ${table.name} ADD CONSTRAINT ${sqlPkName} PRIMARY KEY (${sqlPkColumns});`
		}

		// unique Indexes SQL
		let sqlUniqueIndex = ''
		table.indexes?.forEach((index: DbIndex) => {
			if (index.unique) {
				const sqlIndexName = this.renderIndexName(table, index)
				const sqlColumns = index.columns.replace(',\\s*', ', ')
				sqlUniqueIndex +=
					line + `ALTER TABLE ${table.name} ADD CONSTRAINT ${sqlIndexName} UNIQUE (${sqlColumns});`
			}
		})

		// non-unique Indexes SQL
		let sqlNoUniqueIndex = ''
		table.indexes?.forEach((index: DbIndex) => {
			if (!index.unique) {
				const sqlIndexName = this.renderIndexName(table, index)
				const sqlColumns = index.columns.replace(',\\s*', ', ')
				sqlNoUniqueIndex += line + `CREATE INDEX ${sqlIndexName} ON ${table.name} (${sqlColumns});`
			}
		})

		// Generate table sql
		// Create table
		result.sqlCreateTable =
			''.padEnd(32, '-') +
			line +
			`-- Create table: '${table.name}'` +
			line +
			''.padEnd(32, '-') +
			line +
			`CREATE TABLE ${table.name} (` +
			sqlColumn +
			line +
			');' +
			sqlPrimaryKey +
			sqlUniqueIndex +
			sqlNoUniqueIndex

		// Generate Foreign key SQL
		if (table.foreignKeys.length > 0) {
			result.sqlCreateFK = `-- Create foreign keys for table: '${table.name}'`
			table.foreignKeys.forEach((foreignKey) => {
				const sqlFkName = this.renderFkName(table, foreignKey)
				const sqlColumns = foreignKey.columns.replace(',\\s*', ', ')
				const sqlRefInfo = `${foreignKey.refTable}(${foreignKey.refColumns.replace(',\\s*', ', ')})`
				const sqlOptions = foreignKey.options!.length > 0 ? foreignKey.options : ''
				result.sqlCreateFK +=
					line +
					`ALTER TABLE ${table.name}` +
					line +
					`  ADD CONSTRAINT ${sqlFkName}` +
					line +
					`  FOREIGN KEY (${sqlColumns})` +
					line +
					`  REFERENCES ${sqlRefInfo}${sqlOptions};`
			})
		}

		// Column comment
		let sqlComment = ''
		if (withComment) {
			sqlComment = `COMMENT ON TABLE ${table.name} IS ${SqlHelper.toSQL(table.comment)};`
			table.columns.forEach((column: DbColumn) => {
				let sqlColumnComment = this.getColumnComment(column)
				if (sqlColumnComment) {
					sqlColumnComment = SqlHelper.toSQL(sqlColumnComment)
					sqlComment +=
						line + `COMMENT ON COLUMN ${table.name}.${column.name} IS ${sqlColumnComment};`
				}
			})
		}
		if (sqlComment) {
			result.sqlCreateTable += line + sqlComment
		}

		return result
	}

	private getSqlDropTableSingle(table: DbTable): string {
		const sSQL =
			'  SELECT COUNT(*) INTO v_table_is_exists' +
			line +
			'  FROM user_tables' +
			line +
			`  WHERE lower(table_name) = lower('${table.name}');` +
			line +
			'  IF v_table_is_exists != 0 THEN' +
			line +
			`    execute immediate 'DROP TABLE ${table.name} PURGE';` +
			line +
			'  END IF;'

		return sSQL
	}

	private getSqlDropTableSinglePost(): string {
		const sSQL = 'END;' + line + '/' + line

		return sSQL
	}

	private getSqlDropTableSinglePre(): string {
		const sSQL = 'DECLARE' + line + '  v_table_is_exists integer;' + line + 'BEGIN'

		return sSQL
	}

	private getSqlRemoveFKPost(): string {
		const sSQL =
			'-- Remove temporary store procedure for remove foreign key' +
			line +
			`DROP PROCEDURE ${this.SP_REMVOE_FK_NAME};`

		return sSQL
	}

	private getSqlRemoveFKPre(): string {
		const sSQL =
			'-- temporary store procedure for remove foreign key' +
			line +
			`CREATE or REPLACE PROCEDURE ${this.SP_REMVOE_FK_NAME}(` +
			line +
			'    a_table_name IN VARCHAR2' +
			line +
			') IS' +
			line +
			'  v_fk_name varchar2(250);' +
			line +
			'  CURSOR c_fk IS' +
			line +
			'    SELECT UC.constraint_name' +
			line +
			'      FROM user_constraints UC' +
			line +
			'     WHERE lower(UC.table_name) = lower(a_table_name)' +
			line +
			"       AND UC.constraint_type = 'R';" +
			line +
			'BEGIN' +
			line +
			'' +
			line +
			'  OPEN c_fk;' +
			line +
			'  LOOP' +
			line +
			'    FETCH c_fk INTO v_fk_name;' +
			line +
			'    EXIT WHEN c_fk%NOTFOUND;' +
			line +
			"      EXECUTE IMMEDIATE 'ALTER TABLE ' || a_table_name || ' DROP CONSTRAINT ' || v_fk_name;" +
			line +
			'    END LOOP;' +
			line +
			'  CLOSE c_fk;' +
			line +
			`END ${this.SP_REMVOE_FK_NAME};`
		return sSQL
	}

	private getSqlRemoveFkSingle(table: DbTable): string {
		const sSQL =
			`-- Remove ${table.name} foreign key constraint` +
			line +
			`CALL ${this.SP_REMVOE_FK_NAME}('${table.name}');`

		return sSQL
	}

	public renderDefaultName(table: DbTable, column: DbColumn): string {
		const sSQL = this.RenderObjectName(super.renderDefaultName(table, column))

		return sSQL
	}

	public renderFkName(table: DbTable, foreignKey: DbForeignKey): string {
		const sSQL = this.RenderObjectName(super.renderFkName(table, foreignKey))

		return sSQL
	}

	public renderIndexName(table: DbTable, index: DbIndex): string {
		const sSQL = this.RenderObjectName(super.renderIndexName(table, index))
		return sSQL
	}

	private RenderObjectName(ObjectName: string): string {
		let newObjectName: string
		let objectNameIndex: number
		newObjectName = ObjectName

		if (newObjectName.length > this.MAX_OBJECT_NAME_LENGTH) {
			newObjectName = newObjectName.substring(0, this.MAX_OBJECT_NAME_LENGTH - 4)

			objectNameIndex = this.p_colObjectNames[newObjectName]
			if (!objectNameIndex) {
				objectNameIndex = 0
				this.p_colObjectNames[newObjectName] = newObjectName
			} else {
				objectNameIndex += 1
				this.p_colObjectNames[newObjectName] = objectNameIndex
			}
			newObjectName += objectNameIndex.toString().padEnd(4, '0')
		}

		return newObjectName
	}

	public renderPkName(table: DbTable): string {
		const sSQL = this.RenderObjectName(super.renderPkName(table))

		return sSQL
	}
}
